package pers.nickerg.conver;

import org.apache.commons.io.FileUtils;
import pers.nickerg.bean.ColumnBean;
import pers.nickerg.bean.TableBean;
import pers.nickerg.config.Constant;
import pers.nickerg.pojo.TableColumnWrapper;
import pers.nickerg.util.OracleUtil;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;

/**
 * 将Oracle的表迁移到Mysql
 *
 * @author PF0P65Z6
 */
public class CreateTableToMysql {
    public static void main(String args[]) throws Exception {
        long start = System.currentTimeMillis();

        int count = 0;
        String fileName = "mysql.sql";
        File file = new File(Constant.FILE_PATH + fileName);
        if (file.exists()) {
            file.delete();
        }
        file.createNewFile();
        List<TableBean> tableList = OracleUtil.getAllTableName();
        List<String> sqlLineList = new ArrayList<>();
        for (TableBean tableBean : tableList) {
            String createSql = createTable(tableBean.getTableName(), tableBean.getTableComment());
            sqlLineList.add(createSql);
            count++;
        }
        //    	createTable("TEST","test");
        //写入文件中
        FileUtils.writeLines(file, sqlLineList);

        long end = System.currentTimeMillis();
        System.out.println("oracle表结构迁移到mysql已完成，总共涉及表" + count + "张,用时" + (end - start) / 1000 + "秒");
    }

    /**
     * 将Oracle的表复制到MySQL中
     */
    public static String createTable(String tableName, String tableComment) {
        Connection conn = null;
        PreparedStatement ps = null;

        String createTableSql = getMysqlCreateSql(tableName, tableComment);
        System.out.println(createTableSql);

        // try {
        // 	conn = MysqlUtil.getConnection();
        //     ps = conn.prepareStatement(createTableSql);
        //     ps.execute();
        // } catch (SQLException e) {
        //     e.printStackTrace();
        // }finally{
        // 	MysqlUtil.releaseDB(ps);
        // }
        return createTableSql;
    }

    /**
     * 把oracle创建表语句改为mysql创建表语句
     */
    private static String getMysqlCreateSql(String tableName, String tableComment) {
        List<ColumnBean> colList = OracleUtil.getColList(tableName);
        StringBuffer sb = new StringBuffer();
        String primaryKey = "";

        sb.append("CREATE TABLE ").append(tableName.toUpperCase()).append("(");
        Integer dataLength = 0;
        for (int i = 0; i < colList.size(); i++) {
            ColumnBean col = colList.get(i);
            TableColumnWrapper columnWrapper = getMySQLType(tableName, col, dataLength);
            String mySQLType = columnWrapper.getType();
            dataLength += columnWrapper.getDataLength();
            sb.append("`" + col.getColumnName() + "` ").append(mySQLType);

            //设置字段是否为空
            if ("N".equalsIgnoreCase(col.getNullAble())) {
                sb.append(" NOT NULL");
            }
            //设置主键
            if (null != col.getPrimaryKey() && !"".equals(col.getPrimaryKey())) {
                // varchar key长度限制
                if (dataLength < 767) {
                    String columnType = col.getColumnType();
                    if (!"BLOB".equalsIgnoreCase(columnType) && !"TEXT".equalsIgnoreCase(columnType)
                            && !"VARCHAR2".equalsIgnoreCase(columnType)) {
                        if (!"".equals(primaryKey)) {
                            primaryKey += "," + col.getPrimaryKey();
                        } else {
                            primaryKey += col.getPrimaryKey();
                            //sb.append(" AUTO_INCREMENT");
                        }
                    }
                }
            }
            //设置字段注释
            if (null != col.getComments() && !"".equals(col.getComments())) {
                sb.append(" COMMENT '" + col.getComments() + "'");
            }
            sb.append(",");
        }
        //设置主键
        if (!"".equals(primaryKey)) {
            sb.append("PRIMARY KEY (" + primaryKey + "),");
            if (primaryKey.split(",").length > 1) {
                System.out.println("WARN:表" + tableName + "，自动递增主键可能存在问题，主键为：" + primaryKey);
            }
        }

        sb = sb.deleteCharAt(sb.length() - 1);
        sb.append(") ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=COMPACT COMMENT='" + tableComment + "' ;");

        return sb.toString();
    }

    /**
     * 把Oracle的Type转成Mysql的Type
     *
     * @return
     */
    private static TableColumnWrapper getMySQLType(String tableName, ColumnBean c, Integer varcharLength) {
        if (c == null) {
            return null;
        }

        int dataLength = c.getDataLength();
        int dataPrecision = c.getDataPrecision();
        int dataScale = c.getDataScale();
        String type = c.getColumnType();
        String resultType = type;
        Integer singleVarcharLength = 0;
        if ("BLOB".equalsIgnoreCase(type) || "CLOB".equalsIgnoreCase(type) || "RAW".equalsIgnoreCase(type)) {
            resultType = "longtext";
        } else if ("DATE".equalsIgnoreCase(type)) {
            resultType = "datetime";
        } else if ("CHAR".equalsIgnoreCase(type)) {
            singleVarcharLength += dataLength;
            resultType = "char";
        } else if ("VARCHAR2".equalsIgnoreCase(type)) {
            if (c.getDataLength() >= 512) {
                resultType = "text";
            } else {
                // martin mysqlUtf8 65535限制
                varcharLength += dataLength;
                if (varcharLength >= 65535) {
                    resultType = "text";
                } else {
                    resultType = "varchar(" + dataLength + ")";
                    singleVarcharLength = dataLength;
                }
            }
        } else if ("NUMBER".equalsIgnoreCase(type)) {
            if (dataScale > 0) {
                resultType = "decimal(" + dataPrecision + "," + dataScale + ")";
            } else {
                if (dataPrecision == 0) {
                    dataPrecision = dataLength;
                }

                if (dataPrecision <= 3) {
                    resultType = "tinyint(" + dataPrecision + ")";
                } else if (dataPrecision <= 5) {
                    resultType = "smallint(" + dataPrecision + ")";
                } else if (dataPrecision <= 7) {
                    resultType = "mediumint(" + dataPrecision + ")";
                } else if (dataPrecision <= 10) {
                    resultType = "int(" + dataPrecision + ")";
                } else {
                    resultType = "bigint(" + dataPrecision + ")";
                }
            }
        } else if ("NVARCHAR2".equalsIgnoreCase(type)) {
            resultType = "nvarchar(" + dataLength + ")";
            singleVarcharLength += dataLength;
            // } else if ("TIMESTAMP".equalsIgnoreCase(type)) {
            //     if (dataLength>0) {
            //         return "datetime(" + dataLength + ")";
            //     }
            //     return type.replace("TIMESTAMP", "datetime");
        } else if (type.contains("TIMESTAMP")) {
            resultType = "datetime";
        } else if ("FLOAT".equalsIgnoreCase(type)) {
            resultType = "double";
        }

        System.out.println("WARN:表" + tableName + ",字段" + c.getColumnName() + "，转换mysql，使用默认类型" + c.getColumnType());
        TableColumnWrapper wrapper = new TableColumnWrapper();
        wrapper.setType(c.getColumnType());
        wrapper.setType(resultType);
        wrapper.setDataLength(singleVarcharLength);
        return wrapper;
    }
}